from typing import List
from numpy.core.fromnumeric import put, size
import pymysql
import xlrd
import random
import pandas as pd
import datetime


# # 补全进货数据
# df = pd.read_excel("supermarket2.xls")
# list = ['支付宝','微信','现金','银联']

# for i in df.index:
#     # t = random.randint(0,3)
#     # inportprice = random.randint(5,10)
#     # # df['支付方式'][i+1] = list[t]
#     # df['进货价格'][i+1] = inportprice
#     df['商品编号'][i+1] = i+1
# df.to_excel('supermarket2.xls')

# 连接数据库
goods = xlrd.open_workbook("supermarket2.xls")
database = pymysql.connect(host="localhost", user = "root", passwd = "13751425366",db = "market", charset="utf8")
worksheet = goods.sheet_by_index(0)
print(worksheet)
# 插入数据库

# 获取游标对象，用于逐行遍历数据库数据
cursor = database.cursor()

query = """ INSERT INTO bus_inport (id, providerid, paytype, inporttime, operateperson, number, remark, inportprice, goodsid) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) """

for r in range(1,worksheet.nrows):
    id = r
    providerid = worksheet.cell(r,1).value
    paytype = worksheet.cell(r,2).value
    inporttime = datetime.datetime.now()
    operateperson = worksheet.cell(r,4).value
    number = worksheet.cell(r,5).value
    remark = worksheet.cell(r,6).value
    inportprice = worksheet.cell(r,7).value
    goodsid = r
    
    values = (str(id), providerid, paytype, inporttime, operateperson, number, remark, inportprice, str(goodsid))
    cursor.execute(query, values)

cursor.close()

database.commit()

database.close()

